package edu.yuhf.jdbcpart;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

import edu.yuhf.dbutil.JdbcTemplate;
import edu.yuhf.dbutil.ResultSetHandler;
import edu.yuhf.domain.User;
import edu.yuhf.domain.User2List;

public class UserTableOperationForJdbcTemplate {
	public List<User> queryAll0() {
	 		String sql="select * from users";
	 		List<User> list=JdbcTemplate.query(sql, (rs)->{
	 			List<User> list0=new ArrayList<>();
	 			try {
	 				while(rs.next()) {
	 					User user=new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),LocalDateTime.now());
	 					list0.add(user);				
	 				}
	 			} catch (SQLException e) {
	 				e.printStackTrace();
	 			}
	 			return list0;
	 		},new Object[] {});
	 		return list;
	 	}
	 	
	 	public List<User> queryAll1() {
	 		String sql="select * from users";
	 		List<User> list=JdbcTemplate.query(sql,new ResultSetHandler<List<User>>() {
	 			public List<User> handler(ResultSet rs) {
	 				List<User> list0=new ArrayList<>();
	 				try {
	 					while(rs.next()) {
	 						User user=new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),LocalDateTime.now());
	 						list0.add(user);				
	 					}
	 				} catch (SQLException e) {
	 					e.printStackTrace();
	 				}
	 				return list0;
	 			}
	 		},new Object[] {});
	 		return list;
	 	}	
	 	
	 	public List<User> queryAll2() {
	 	 		String sql="select * from users";
	 	 		List<User> list=JdbcTemplate.query(sql,new User2List(),new Object[] {});
	 	 		return list;
	 	 	}	
	 	
	 	public User queryById(int id) {
	 		String sql="select * from users where id=?";
	 		Connection connection=DBConnection.getConnection();
	 		User user=null;
	 		try {
	 			PreparedStatement psmt=connection.prepareStatement(sql);
	 			psmt.setInt(1, id);
	 			ResultSet rs=psmt.executeQuery();
	 			if(rs.next()) {
	 				user=new User(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),LocalDateTime.now());
	 			}
	 		} catch (SQLException e) {
	 			System.out.println(UserTableOperation.class.getName()+": query By Id method error,message is "+e.getMessage());
	 		}
	 		return user;
	 	}
	 	public int addUser(User user) {
	 		String sql="insert into users(id,name,password,sex) values(users_id.nextval,?,?,?)";
	 		return JdbcTemplate.update(sql, user.getName(),user.getPassword(),user.getSex());
	 	}

}
